package uf.audit.util.poi;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import uf.audit.util.Message;


public class POIExtractor {
	
	private static final Logger log = Logger.getLogger(POIExtractor.class);
	
	protected static Workbook createWorkbook(InputStream inp) throws IOException, InvalidFormatException {
		if (!inp.markSupported()) {
			inp = new PushbackInputStream(inp, 8);
		}
		if (POIFSFileSystem.hasPOIFSHeader(inp)) {
			return new HSSFWorkbook(inp);
		}
		if (POIXMLDocument.hasOOXMLHeader(inp)) {
			return new XSSFWorkbook(OPCPackage.open(inp));
		}
		throw new IllegalArgumentException("Your InputStream was neither an OLE2 stream, nor an OOXML stream");
	}

	protected static String getCellFormatValue(Cell cell) {
		String cellvalue = "";
		if (cell != null) {
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_NUMERIC:
			case HSSFCell.CELL_TYPE_FORMULA: {
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					Date date = cell.getDateCellValue();
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					cellvalue = sdf.format(date);
				} else {
					DecimalFormat df = new DecimalFormat("0");
					cellvalue = df.format(cell.getNumericCellValue());
				}
				break;
			}
			case HSSFCell.CELL_TYPE_STRING:
				cellvalue = cell.getRichStringCellValue().getString();
				break;
			default:
				cellvalue = " ";
			}
		} else {
			cellvalue = "";
		}
		return cellvalue;
	}
	
	private static List<Map<String, String>> readSheetData(Sheet sheet, List<String> headerList){
		List<Map<String, String>> result = new ArrayList<Map<String, String>>();
		Row row = sheet.getRow(0);
		int rowNum = sheet.getLastRowNum();
		int colNum = row.getPhysicalNumberOfCells();
		for (int i = 0; i < colNum; i++) {
			headerList.add(getCellFormatValue(row.getCell(i)));
		}
		for (int i = 1; i <= rowNum; i++) {
			LinkedHashMap<String, String> rowResult = new LinkedHashMap<String, String>();
			row = sheet.getRow(i);
			for (int j = 0; j < colNum; j++) {
				rowResult.put(headerList.get(j), getCellFormatValue(row.getCell(j)));
			}
			result.add(rowResult);
		}
		return result;
	}
	
	private static List<Map<String, String>> readSheetData(Sheet sheet, List<String> headerList, SheetConfig sheetConfig){
		List<Map<String, String>> result = new ArrayList<Map<String, String>>();
		Row row = sheet.getRow(0);
		int rowNum = sheet.getLastRowNum();
		int colNum = row.getPhysicalNumberOfCells();
		
		Map<String, String> importColumnMap = sheetConfig.getImportColumnMap();
		for (int i = 0; i < colNum; i++) {
			String cellValue = getCellFormatValue(row.getCell(i));
			if(importColumnMap.containsKey(cellValue)){
				headerList.add(importColumnMap.get(cellValue));
			}else{
				headerList.add(cellValue);
			}
		}
		for (int i = 1; i <= rowNum; i++) {
			LinkedHashMap<String, String> rowResult = new LinkedHashMap<String, String>();
			row = sheet.getRow(i);
			for (int j = 0; j < colNum; j++) {
				rowResult.put(headerList.get(j), getCellFormatValue(row.getCell(j)));
			}
			result.add(rowResult);
		}
		return result;
	}

	/**
	 * 公用读取excel方法
	 * @param file       被读取excel文件对象
	 * @param headerList 被读取excel文件第一行各列对应关键字
	 * @return
	 */
	public static List<Map<String, String>> excelExtractor(File file, List<String> headerList) {
		Workbook wb = null;
		FileInputStream is = null;
		List<Map<String, String>> result = null;
		try {
			is = new FileInputStream(file);
			wb = createWorkbook(is);
			result = readSheetData(wb.getSheetAt(0), headerList);
		} catch (IOException e) {
			e.printStackTrace();
			log.error(e.getMessage(), e);
		} catch (InvalidFormatException e) {
			e.printStackTrace();
			log.error(e.getMessage(), e);
		} finally {
			try {
				if (is != null) {
					is.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return result;
	}
	
	/**
	 * 公用读取excel文件的工具类(本方法不能用于存储大数据量的excel读取)
	 * @param file               excel文件对象
	 * @param multiHeaderList    表头信息（多sheet页表头）
	 * @param maxReadSheetNumber 读取的最多sheet页个数
	 * @return
	 */
	public static Message excelExtractor(File file, List<List<String>> multiHeaderList, int maxReadSheetNumber) {
		Workbook wb = null;
		FileInputStream is = null;
		Message message = new Message();
		try {
			is = new FileInputStream(file);
			wb = createWorkbook(is);
			List<List<Map<String, String>>> resultList = new ArrayList<List<Map<String, String>>>();
			
			int sheetNumber = Math.min(wb.getNumberOfSheets(), maxReadSheetNumber);
			for(int sheetIndex = 0; sheetIndex < sheetNumber; sheetIndex++){
				List<String> curHeadList = new ArrayList<String>();
				List<Map<String, String>> curResult = readSheetData(wb.getSheetAt(sheetIndex), curHeadList);
				
				multiHeaderList.add(curHeadList);
				resultList.add(curResult);
			}
			
			message.setSuccess(true);
			message.setReturnObj(resultList);
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e.getMessage(), e);
			message.setSuccess(false);
			message.setMsg("读取excel时出错");
		} finally {
			try {
				if (is != null) {
					is.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return message;
	}
	
	public static Message excelExtractor(File file, List<List<String>> multiHeaderList, List<SheetConfig> sheetConfigList) {
		Workbook wb = null;
		FileInputStream is = null;
		Message message = new Message();
		try {
			is = new FileInputStream(file);
			wb = createWorkbook(is);
			int sheetNumber = wb.getNumberOfSheets();
			if(sheetNumber < sheetConfigList.size()){
				message.setSuccess(false);
				message.setMsg("缺少足够的数据");
			}else{
				List<List<Map<String, String>>> resultList = new ArrayList<List<Map<String, String>>>();
				sheetNumber = Math.min(wb.getNumberOfSheets(), sheetConfigList.size());
				for(int sheetIndex = 0; sheetIndex < sheetNumber; sheetIndex++){
					List<String> curHeadList = new ArrayList<String>();
					SheetConfig sheetConfig = sheetConfigList.get(sheetIndex);
					
					List<Map<String, String>> curResult = readSheetData(wb.getSheetAt(sheetIndex), curHeadList, sheetConfig);
					
					multiHeaderList.add(curHeadList);
					resultList.add(curResult);
				}
				
				message.setSuccess(true);
				message.setReturnObj(resultList);
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e.getMessage(), e);
			message.setSuccess(false);
			message.setMsg("读取excel时出错");
		} finally {
			try {
				if (is != null) {
					is.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return message;
	}
	
	public static void main(String[] args) {
//		String xlsPath = "E:\\项目文档\\标准\\测试文件\\报表测试模板011462332209971.xls";
//		File xlsFile = new File(xlsPath);
//		
//		List<List<String>> headerList = new ArrayList<List<String>>();
//		
//		Message message = POIExtractor.excelExtractor(xlsFile, headerList, 3);
//		List<List<Map<String, String>>> resultList = (List<List<Map<String, String>>>) message.getReturnObj();
//		
//		System.out.println(resultList.size());
	}
}
